﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.Data;
using System.Collections.ObjectModel;

namespace PurchaseManager
{
    public class DBManager
    {
        public SQLiteConnection _sqlCon = null;
        // 변경 과정중...
        public string _dbPath = string.Empty;

        public DBManager()
        {
            string _dbPath = @"purchase.db";

            DB_Load(_dbPath);
        }

        public void DB_Load(string path)
        {
            if (_sqlCon != null)
            {
                _sqlCon.Close();
                _sqlCon = null;
            }

            if (_sqlCon == null)
            {
                _dbPath = path;
                string _dbConnection = string.Format("Data Source={0}", _dbPath);
                _sqlCon = new SQLiteConnection(_dbConnection);
                CreateTable();
            }
        }

        public void CreateTable()
        {
            // Table 있는지 체크
            _sqlCon.Open();

            //  Create table.
            using (SQLiteCommand command1 = _sqlCon.CreateCommand())
            {
                SQLiteDataReader reader = null;

                command1.CommandText = "SELECT COUNT(*) FROM sqlite_master WHERE name='Category';";
                reader = command1.ExecuteReader();

                int count = 0;

                while (reader.HasRows && reader.Read())
                {
                    count = reader.GetInt32(0);
                }

                if (count <= 0)
                {
                    SQLiteCommand command = _sqlCon.CreateCommand();

                    command.CommandText = @"CREATE TABLE [Category] (
                                              [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
                                              [name] [VARCHAR(300)],
                                              [codePrefix] [VARCHAR(10)],
                                              [lastNumber] INTEGER,
                                              [memo] TEXT);";
                    command.ExecuteNonQuery();

                    command.CommandText = @"CREATE TABLE [Company] (
                                              [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
                                              [name] [VARCHAR(300)],
                                              [address] [VARCHAR(500)],
                                              [phonenumber] [VARCHAR(500)],
                                              [memo] TEXT);";
                    command.ExecuteNonQuery();

                    command.CommandText = @"CREATE TABLE [SellItem] (
                                              [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
                                              [productId] INTEGER, 
                                              [quantity] INTEGER, 
                                              [date] INT64,
                                              [memo] TEXT);";
                    command.ExecuteNonQuery();

                    command.CommandText = @"CREATE TABLE [PurchaseItem] (
                                              [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
                                              [productId] INTEGER, 
                                              [quantity] INTEGER, 
                                              [date] INT64,
                                              [memo] TEXT);";
                    command.ExecuteNonQuery();

                    command.CommandText = @"CREATE TABLE [Product] (
                                              [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
                                              [codeName] [VARCHAR(300)], 
                                              [companyId] INTEGER, 
                                              [categoryId] INTEGER, 
                                              [itemName] [VARCHAR(300)], 
                                              [companyItemName] DECIMAL, 
                                              [parentId] INTEGER, 
                                              [hasChild] INTEGER, 
                                              [option1] [VARCHAR(300)], 
                                              [option2] [VARCHAR(300)], 
                                              [option3] [VARCHAR(300)], 
                                              [primeCost] DECIMAL, 
                                              [sellCost] DECIMAL,
                                              [image] BLOB,
                                              [status] INTEGER, 
                                              [memo] TEXT);";
                    command.ExecuteNonQuery();
                }
            }

            _sqlCon.Close();
        }

        #region - Retrieve Functions

        public ObservableCollection<Category> RetrieveCategoryList()
        {
            ObservableCollection<Category> categoryList = new ObservableCollection<Category>();

            _sqlCon.Open();
            SQLiteDataReader reader = null;

            try
            {
                SQLiteCommand command = new SQLiteCommand(_sqlCon);
                command.CommandType = CommandType.Text;
                command.CommandText = @"select id, name, codePrefix, lastNumber, memo from Category";

                reader = command.ExecuteReader();

                while (reader.HasRows && reader.Read())
                {
                    int id = reader.GetInt32(0);
                    string name = reader.GetString(1);
                    string codePrefix = reader.GetString(2);
                    int lastNumber = reader.GetInt32(3);
                    string memo = reader.GetString(4);

                    Category category = new Category(id, name, codePrefix, lastNumber, memo);

                    categoryList.Add(category);
                }
            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            reader.Close();
            _sqlCon.Close();

            return categoryList;
        }

        public ObservableCollection<Company> RetrieveCompanyList()
        {
            ObservableCollection<Company> companyList = new ObservableCollection<Company>();

            _sqlCon.Open();
            SQLiteDataReader reader = null;

            try
            {
                SQLiteCommand command = new SQLiteCommand(_sqlCon);
                command.CommandType = CommandType.Text;
                command.CommandText = @"select id, name, address, phonenumber, memo from Company";

                reader = command.ExecuteReader();

                while (reader.HasRows && reader.Read())
                {
                    int id = reader.GetInt32(0);
                    string name = reader.GetString(1);
                    string address = reader.GetString(2);
                    string phonenumber = reader.GetString(3);
                    string memo = reader.GetString(4);

                    Company company = new Company(id, name, address, phonenumber, memo);

                    companyList.Add(company);
                }
            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            reader.Close();
            _sqlCon.Close();

            return companyList;
        }

        public ObservableCollection<Product> RetrieveProductList()
        {
            ObservableCollection<Product> itemList = new ObservableCollection<Product>();

            _sqlCon.Open();
            SQLiteDataReader reader = null;

            try
            {
                SQLiteCommand command = new SQLiteCommand(_sqlCon);
                command.CommandType = CommandType.Text;
                command.CommandText = @"select id, codeName, companyId, categoryId, itemName, companyItemName, parentId, hasChild, option1, option2, option3,
                                                primeCost, sellCost, image, status, memo from Product";

                reader = command.ExecuteReader();

                while (reader.HasRows && reader.Read())
                {
                    Product item = new Product();

                    item.Id = reader.GetInt32(0);

                    item.CodeName = reader.GetString(1);
                    item.CompanyId = reader.GetInt32(2);
                    item.CategoryId = reader.GetInt32(3);
                    item.ItemName = reader.GetString(4);
                    item.CompanyItemName = reader.GetString(5);
                    item.ParentId = reader.GetInt32(6);
                    item.HasChild = reader.GetBoolean(7); // 확인 필요
                    item.Option1 = reader.GetString(8);
                    item.Option2 = reader.GetString(9);
                    item.Option3 = reader.GetString(10);
                    item.PrimeCost = reader.GetDecimal(11);
                    item.SellCost = reader.GetDecimal(12);
                    // item.Image = reader.GetBytes(13);// .GetStream(16);
                    item.Status = reader.GetInt32(14);
                    item.Memo = reader.GetString(15);

                    itemList.Add(item);
                }
            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            reader.Close();
            _sqlCon.Close();

            return itemList;
        }

        public ObservableCollection<Item> RetrieveSellItemList()
        {
            ObservableCollection<Item> itemList = new ObservableCollection<Item>();

            _sqlCon.Open();
            SQLiteDataReader reader = null;

            try
            {
                SQLiteCommand command = new SQLiteCommand(_sqlCon);
                command.CommandType = CommandType.Text;
                command.CommandText = @"select id, productId, quantity, date, memo from SellItem";

                reader = command.ExecuteReader();

                while (reader.HasRows && reader.Read())
                {
                    Item item = new Item();

                    item.Id = reader.GetInt32(0);
                    item.ProductId = reader.GetInt32(1);
                    item.Quantity = reader.GetInt32(2);
                    item.Date = reader.GetInt64(3);
                    item.Memo = reader.GetString(4);

                    itemList.Add(item);
                }
            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            reader.Close();
            _sqlCon.Close();

            return itemList;
        }

        public ObservableCollection<Item> RetrievePurchaseItemList()
        {
            ObservableCollection<Item> itemList = new ObservableCollection<Item>();

            _sqlCon.Open();
            SQLiteDataReader reader = null;

            try
            {
                SQLiteCommand command = new SQLiteCommand(_sqlCon);
                command.CommandType = CommandType.Text;
                command.CommandText = @"select id, productId, quantity, date, memo from PurchaseItem";

                reader = command.ExecuteReader();

                while (reader.HasRows && reader.Read())
                {
                    Item item = new Item();

                    item.Id = reader.GetInt32(0);
                    item.ProductId = reader.GetInt32(1);
                    item.Quantity = reader.GetInt32(2);
                    item.Date = reader.GetInt64(3);
                    item.Memo = reader.GetString(4);

                    itemList.Add(item);
                }
            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            reader.Close();
            _sqlCon.Close();

            return itemList;
        }

        #endregion - Retrieve Functions

        #region - Insert
        public void InsertCategoryList(ref List<Category> categoryList)
        {
            _sqlCon.Open();

            try
            {
                BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(_sqlCon);

                foreach (Category category in categoryList)
                {
                    string query = string.Format(@"insert or replace into Category (name, codePrefix, lastNumber, memo) VALUES ('{0}', '{1}', {2}, '{3}')",
                        category.Name, category.CodePrefix, category.LastNumber, category.Memo);

                    command.CommandText = query;
                    command.ExecuteScalar();
                }

                Commit();

            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            _sqlCon.Close();
        }

        public void InsertCompanyList(ref List<Company> companyList)
        {
            _sqlCon.Open();

            try
            {
                BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(_sqlCon);

                foreach (Company company in companyList)
                {
                    string query = string.Format(@"insert or replace into Company (name, address, phonenumber, memo) VALUES ('{0}', '{1}', '{2}', '{3}')",
                        company.Name, company.Address, company.PhoneNumber, company.Memo);

                    command.CommandText = query;
                    command.ExecuteScalar();
                }

                Commit();

            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            _sqlCon.Close();
        }

        public void InsertProductList(ref List<Product> itemList)
        {
            _sqlCon.Open();

            try
            {
                BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(_sqlCon);

                foreach (Product item in itemList)
                {
                    string query = string.Format(@"insert or replace into Product (codeName, companyId, categoryId, itemName, companyItemName, parentId, hasChild, option1, option2, option3, 
                                                                                primeCost, sellCost, memo, purchaseDate, image, status) 
                                                            VALUES ('{0}', {1}, {2}, '{3}', '{4}', {5}, {6}, '{7}', '{8}', '{9}', {10}, {11}, '{12}', {13}, {14})",
                                                            item.CodeName, item.CompanyId, item.CategoryId, item.ItemName, item.CompanyItemName, item.ParentId, item.HasChild, item.Option1, item.Option2, item.Option3,
                                                            item.PrimeCost, item.SellCost, item.Memo, item.Image, item.Status);

                    command.CommandText = query;
                    command.ExecuteScalar();
                }

                Commit();

            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            _sqlCon.Close();
        }

        public void InsertSellItemList(ref List<Item> itemList)
        {
            _sqlCon.Open();

            try
            {
                BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(_sqlCon);

                foreach (Item item in itemList)
                {
                    string query = string.Format(@"insert or replace into SellItem (productId, quantity, date, memo) 
                                                            VALUES ({0}, {1}, {2}, '{3}')",
                                                            item.ProductId, item.Quantity, item.Date, item.Memo);

                    command.CommandText = query;
                    command.ExecuteScalar();
                }

                Commit();

            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            _sqlCon.Close();
        }

        public void InsertPurchaseItemList(ref List<Item> itemList)
        {
            _sqlCon.Open();

            try
            {
                BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(_sqlCon);

                foreach (Item item in itemList)
                {
                    string query = string.Format(@"insert or replace into PurchaseItem (productId, quantity, date, memo) 
                                                            VALUES ({0}, {1}, {2}, '{3}')",
                                                            item.ProductId, item.Quantity, item.Date, item.Memo);

                    command.CommandText = query;
                    command.ExecuteScalar();
                }

                Commit();

            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            _sqlCon.Close();
        }

        #endregion - Insert

        #region - Delete
        public void DeleteAllCategories()
        {
            _sqlCon.Open();

            try
            {
                BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(_sqlCon);

                string query = string.Format(@"delete from Category");

                command.CommandText = query;
                command.ExecuteScalar();

                Commit();

            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            _sqlCon.Close();
        }

        public void DeleteAllCompanies()
        {
            _sqlCon.Open();

            try
            {
                BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(_sqlCon);

                string query = string.Format(@"delete from Company");

                command.CommandText = query;
                command.ExecuteScalar();

                Commit();

            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            _sqlCon.Close();
        }

        public void DeleteAllProducts()
        {
            _sqlCon.Open();

            try
            {
                BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(_sqlCon);

                string query = string.Format(@"delete from Product");

                command.CommandText = query;
                command.ExecuteScalar();

                Commit();

            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            _sqlCon.Close();
        }

        public void DeleteAllSellItems()
        {
            _sqlCon.Open();

            try
            {
                BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(_sqlCon);

                string query = string.Format(@"delete from SellItem");

                command.CommandText = query;
                command.ExecuteScalar();

                Commit();

            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            _sqlCon.Close();
        }

        public void DeleteAllPurchaseItems()
        {
            _sqlCon.Open();

            try
            {
                BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(_sqlCon);

                string query = string.Format(@"delete from PurchaseItem");

                command.CommandText = query;
                command.ExecuteScalar();

                Commit();

            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            _sqlCon.Close();
        }

        public void DeleteCompanyList(ref List<Company> companyList)
        {
            _sqlCon.Open();

            try
            {
                BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(_sqlCon);

                foreach (Company company in companyList)
                {
                    string query = string.Format(@"delete from Company where id = {0}", company.Id);

                    command.CommandText = query;
                    command.ExecuteScalar();
                }

                Commit();

            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            _sqlCon.Close();
        }

        public void DeleteCategoryList(ref List<Category> categoryList)
        {
            _sqlCon.Open();

            try
            {
                BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(_sqlCon);

                foreach (Category category in categoryList)
                {
                    string query = string.Format(@"delete from Category where id = {0}", category.Id);

                    command.CommandText = query;
                    command.ExecuteScalar();
                }

                Commit();

            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            _sqlCon.Close();
        }

        public void DeleteProductList(ref List<Product> itemList)
        {
            _sqlCon.Open();

            try
            {
                BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(_sqlCon);

                foreach (Product item in itemList)
                {
                    string query = string.Format(@"delete from Product where id = {0}", item.Id);

                    command.CommandText = query;
                    command.ExecuteScalar();
                }

                Commit();

            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            _sqlCon.Close();
        }

        public void DeleteSellItemList(ref List<Item> itemList)
        {
            _sqlCon.Open();

            try
            {
                BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(_sqlCon);

                foreach (Item item in itemList)
                {
                    string query = string.Format(@"delete from SellItem where id = {0}", item.Id);

                    command.CommandText = query;
                    command.ExecuteScalar();
                }

                Commit();

            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            _sqlCon.Close();
        }

        public void DeletePurchaseItemList(ref List<Item> itemList)
        {
            _sqlCon.Open();

            try
            {
                BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(_sqlCon);

                foreach (Item item in itemList)
                {
                    string query = string.Format(@"delete from PurchaseItem where id = {0}", item.Id);

                    command.CommandText = query;
                    command.ExecuteScalar();
                }

                Commit();

            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            _sqlCon.Close();
        }


        #endregion - Delete

        #region - Update
        public void UpdateCategoryList(ref List<Category> categoryList)
        {
            _sqlCon.Open();

            try
            {
                BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(_sqlCon);

                foreach (Category category in categoryList)
                {
                    string query = string.Format(@"update Category set name='{0}', codePrefix='{1}', lastNumber={2}, memo='{3}' where id = {4}", 
                        category.Name, category.CodePrefix, category.LastNumber, category.Memo, category.Id);

                    command.CommandText = query;
                    command.ExecuteScalar();
                }

                Commit();

            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            _sqlCon.Close();
        }

        public void UpdateCompanyList(ref List<Company> companyList)
        {
            _sqlCon.Open();

            try
            {
                BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(_sqlCon);

                foreach (Company company in companyList)
                {
                    string query = string.Format(@"update Company set name='{0}', address='{1}', phonenumber='{2}', memo='{3}' where id = {4}",
                        company.Name, company.Address, company.PhoneNumber, company.Memo, company.Id);

                    command.CommandText = query;
                    command.ExecuteScalar();
                }

                Commit();

            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            _sqlCon.Close();
        }

        public void UpdateProductList(ref List<Product> itemList)
        {
            _sqlCon.Open();

            try
            {
                BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(_sqlCon);

                foreach (Product item in itemList)
                {
                    string query = string.Format(@"update Item set codeName='{0}', companyId={1}, categoryId={2}, itemName='{3}', companyItemName='{4}', parentId={5},
                                                                   hasChild={6}, option1='{7}', option2='{8}', option3='{9}', 
                                                                   primeCost={13}, sellCost={14}, image='{15}', memo='{16}' where id = {17}",
                                                    item.CodeName, item.CompanyId, item.CategoryId, item.ItemName, item.CompanyItemName, item.ParentId,
                                                    item.HasChild, item.Option1, item.Option2, item.Option3, item.PrimeCost, item.SellCost, item.Image, item.Memo, item.Id);

                    command.CommandText = query;
                    command.ExecuteScalar();
                }

                Commit();

            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            _sqlCon.Close();
        }

        public void UpdateItemList(ref List<Item> itemList, string tableName)
        {
            _sqlCon.Open();

            try
            {
                BeginTransaction();

                SQLiteCommand command = new SQLiteCommand(_sqlCon);

                foreach (Item item in itemList)
                {
                    string query = string.Format(@"update '{0}' set productId={1}, quantity={2}, date={2}, memo='{3}' where id = {4}",
                                                    tableName, item.ProductId, item.Quantity, item.Date, item.Memo, item.Id);

                    command.CommandText = query;
                    command.ExecuteScalar();
                }

                Commit();

            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            _sqlCon.Close();
        }

        #endregion - Update

        #region - 기타
        private void BeginTransaction()
        {
            SQLiteCommand command = new SQLiteCommand("Begin", _sqlCon);
            command.ExecuteNonQuery();
            command.Dispose();
        }

        private void Commit()
        {
            SQLiteCommand command = new SQLiteCommand("Commit", _sqlCon);
            command.ExecuteNonQuery();
            command.Dispose();
        }

        public void InsertQuery(string query)
        {
            _sqlCon.Open();

            BeginTransaction();

            SQLiteCommand command = new SQLiteCommand(_sqlCon);
            command.CommandText = query;
            command.ExecuteScalar();

            Commit();
            _sqlCon.Close();
        }

        public void Select(string tableName, ref DataTable dt)
        {
            _sqlCon.Open();
            SQLiteDataReader reader = null;
            try
            {
                SQLiteCommand command = new SQLiteCommand(_sqlCon);
                command.CommandType = CommandType.Text;
                command.CommandText = "select * from " + tableName;

                dt.Clear();
                reader = command.ExecuteReader();
                dt.Load(reader);
            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }
            reader.Close();
            _sqlCon.Close();
        }

        public void Select(string tableName, string fieldName, ref DataTable dt)
        {
            _sqlCon.Open();
            SQLiteDataReader reader = null;

            try
            {
                SQLiteCommand command = new SQLiteCommand(_sqlCon);
                command.CommandType = CommandType.Text;
                command.CommandText = "select " + fieldName + " from " + tableName;

                dt.Clear();
                reader = command.ExecuteReader();
                dt.Load(reader);
            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            reader.Close();
            _sqlCon.Close();
        }

        public void Select(string tableName, string fieldName, string condition, ref DataTable dt)
        {
            _sqlCon.Open();
            SQLiteDataReader reader = null;

            try
            {
                SQLiteCommand command = new SQLiteCommand(_sqlCon);
                command.CommandType = CommandType.Text;
                command.CommandText = "select " + fieldName + " from " + tableName + " where " + condition;

                dt.Clear();
                reader = command.ExecuteReader();
                dt.Load(reader);
            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            reader.Close();
            _sqlCon.Close();
        }

        public void Join(string leftTableName, string RightTableName, string fieldName, string condition, ref DataTable dt)
        {
            _sqlCon.Open();
            SQLiteDataReader reader = null;

            try
            {
                SQLiteCommand command = new SQLiteCommand(_sqlCon);
                command.CommandType = CommandType.Text;
                command.CommandText = "select " + fieldName + " from " + leftTableName + " inner join " + RightTableName + " on " + condition;

                dt.Clear();
                reader = command.ExecuteReader();
                dt.Load(reader);
            }
            catch (Exception ex)
            {
                Utils.Log(ex.ToString());
            }

            reader.Close();
            _sqlCon.Close();
        }
        #endregion - 기타
    }
}
